﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;

namespace BusinessLibrary.DAL
{
    class sqlChef_Departement
    {
        private static Chef_Departement ReaderToObject(SqlDataReader dr)
        {
            Chef_Departement _chefdepartement = new Chef_Departement();
            _chefdepartement.Id = int.Parse(dr["Id"].ToString());

            _chefdepartement.Nom = dr["Nom"].ToString();
            _chefdepartement.Prenom = dr["Prenom"].ToString();
            _chefdepartement.Num_tel = dr["Num_tel"].ToString();

            _chefdepartement.Email = dr["Email"].ToString();

            if (!object.ReferenceEquals(dr["Cin"], DBNull.Value)){
                _chefdepartement.Cin = int.Parse(dr["Cin"].ToString()); 
            }

            if (!object.ReferenceEquals(dr["Id_authentification"], DBNull.Value)){
                _chefdepartement.authentificationChef = Authentification.GetAuthentificationById(int.Parse(dr["Id_authentification"].ToString()));
            }
            return _chefdepartement;
        }

        public static Chef_Departement GetChef_DepartementById(int id)
        {
            Chef_Departement chefdepartement = new Chef_Departement();
            string strSQL = @"select * from Chef_departement where UserId=@Id";
            try{
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString)){
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read()){
                        chefdepartement = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return chefdepartement;
        }

        public static int insertChef_Departement(Chef_Departement c)
        {
            int n = 0;
            string strSQL = @"insert into Chef_Departement (Nom,Prenom,CIN,Num_tel,Email,Id_authentification, Id_departement) values(@nom,@prenom,@cin,@num_tel,@email,@id_authentification, @id_departement)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString)){
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@cin", c.Cin);
                    cmd.Parameters.AddWithValue("@nom", c.Nom);
                    cmd.Parameters.AddWithValue("@prenom", c.Prenom);
                    cmd.Parameters.AddWithValue("@num_tel", c.Num_tel);
                    cmd.Parameters.AddWithValue("@email", c.Email);
                    cmd.Parameters.AddWithValue("@id_authentification", c.authentificationChef.Id);
                    cmd.Parameters.AddWithValue("@id_departement", c.departement.Id);
                    n = cmd.ExecuteNonQuery();
                    cnx.Close();                    
                }
            }
            catch (Exception) { }
            return n;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateChef_Departement(Chef_Departement c)
        {
             int n = 0;

             string strSQl = @"UPDATE Chef_departement SET (@nom IS NULL OR Nom=@nom) ,(@cin is null or Cin=@cin),"+
             "(@prenom is null or Prenom=@prenom),(@num_tel  is null or Num_tel=@num_tel), Email=@email,"+
             "Id_authentification=@id_authentification, Id_departement=@id_departement where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    
                     SqlCommand cmd = new SqlCommand(strSQl, cnx);
                    cmd.Parameters.AddWithValue("@cin", c.Cin);
                    cmd.Parameters.AddWithValue("@nom", c.Nom);
                    cmd.Parameters.AddWithValue("@prenom", c.Prenom);
                    cmd.Parameters.AddWithValue("@num_tel", c.Num_tel);
                    cmd.Parameters.AddWithValue("@email", c.Email);
                    cmd.Parameters.AddWithValue("@id_authentification", c.authentificationChef.Id);
                    cmd.Parameters.AddWithValue("@id_departement", c.departement.Id);               
                    n= cmd.ExecuteNonQuery();
                    
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }
       


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deletChef_Departement(int id, int idAuthentif) //à vérifier on ajoute int id_departement ou pas
        { 
            int n = 0;
            string strSQL = @"delete from Chef_departement where id=@id)"; 
            string strSQl2=@"UPDATE Authentification SET ACTIF='0' where id=@idAuth and ACTIF=1";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                     SqlCommand cmd2 = new SqlCommand(strSQl2, cnx);
                    cmd.Parameters.AddWithValue("@id", id);                   
                    cmd2.Parameters.AddWithValue("@idAuth", idAuthentif);
                    if(cmd.ExecuteNonQuery()>0)//si delete ok 
                      n=  cmd2.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }

    }
}